package com.ty.xsl.writer;

import java.io.BufferedOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Map;
import java.util.Properties;
import java.util.UUID;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellUtil;
import org.htmlparser.Parser;
import org.htmlparser.filters.CssSelectorNodeFilter;
import org.htmlparser.tags.TableColumn;
import org.htmlparser.tags.TableRow;
import org.htmlparser.tags.TableTag;
import org.htmlparser.util.NodeList;
import org.htmlparser.util.ParserException;

import com.jd.ty.service.exception.ErrorCode;
import com.jd.ty.service.exception.ServiceException;
import com.ty.xsl.util.ExcelUtil;
import com.ty.xsl.util.XSLColorUtil;

public class ExcelWriter {

	private Logger log = Logger.getLogger(ExcelWriter.class);

	public String writeHTML(Map<String, String> sheets) throws ServiceException {
		Properties p = System.getProperties();
		String tempPath = p.getProperty("java.io.tmpdir");
		String separator = p.getProperty("file.separator");

		String tempFileName = tempPath + separator
				+ UUID.randomUUID().toString() + ".xls";

		BufferedOutputStream out = null;
		try {
			out = new BufferedOutputStream(new FileOutputStream(tempFileName),
					4096);
			Workbook wb = transfer(sheets);
			wb.write(out);

			return tempFileName;
		} catch (Exception e) {
			log.error(e);
			e.printStackTrace();

			throw new ServiceException(ErrorCode.EXCEL_WRITE_ERROR);
		} finally {
			try {
				if (out != null) {
					out.close();
				}
			} catch (IOException e) {
			}
		}
	}

	/**
	 * @function 转换HTML为Excel表格(每个Table为一个Sheet)
	 * @param htmlMap
	 *            key为Sheet页名称，value为HTML字符串
	 * @return 转换后的Excel对象
	 * @throws ParserException
	 * 
	 * 
	 */
	public Workbook transfer(Map<String, String> htmlMap)
			throws ParserException {
		Workbook wb = new HSSFWorkbook();

		if (htmlMap == null || htmlMap.isEmpty()) {
			return wb;
		}

		for (String key : htmlMap.keySet()) {
			//System.out.println(htmlMap.get(key));
			Sheet sheet = wb.createSheet(key);

			NodeList header = parseHeader(htmlMap.get(key));

			for (int i = 0; i < header.size(); i++) {
				TableTag table = (TableTag) header.elementAt(i);

				appendArea(sheet, table, 0);
			}

			int rowOffset = sheet.getLastRowNum() + 1;

			NodeList nodeList = parseBody(htmlMap.get(key));

			for (int i = 0; i < nodeList.size(); i++) {
				TableTag table = (TableTag) nodeList.elementAt(i);

				appendArea(sheet, table, rowOffset);
			}
		}

		return wb;
	}

	/**
	 * @function 解析HTML中的表头
	 * @param html
	 * @return
	 * @throws ParserException
	 * 
	 * 
	 */
	private static NodeList parseHeader(String html) throws ParserException {
		Parser parser = Parser.createParser(html, "UTF-8");

		CssSelectorNodeFilter filter = new CssSelectorNodeFilter(
				".frozengrid-header table");

		return parser.parse(filter);
	}

	/**
	 * @function 解析HTML中的表格内容
	 * @param html
	 * @return
	 * @throws ParserException
	 * 
	 * @author
	 */
	private static NodeList parseBody(String html) throws ParserException {
		Parser parser = Parser.createParser(html, "UTF-8");

		CssSelectorNodeFilter filter = new CssSelectorNodeFilter(
				".frozengrid-body table");

		return parser.parse(filter);
	}

	/**
	 * @function 向Excel工作薄中追加一页
	 * @param sheet
	 *            Excel工作薄
	 * @param table
	 *            HTML表格
	 * @param rowOffset
	 *            行偏移量
	 * @throws ParserException
	 * 
	 * 
	 */
	private static void appendArea(Sheet sheet, TableTag table, int rowOffset)
			throws ParserException {
		int rowHidden = 0;

		for (int i = 0; i < table.getRowCount(); i++) {
			TableRow tr = table.getRow(i);
			StyleAttribute trStyle = StyleAttrParser.parse(tr
					.getAttribute("style"));
			if (trStyle != null) {
				if ("none".equalsIgnoreCase(trStyle.getDisplay())) {
					rowHidden++;
					continue;
				}
			}

			int rowIndex = i + rowOffset - rowHidden;

			for (int j = 0; j < tr.getColumnCount(); j++) {
				TableColumn td = tr.getColumns()[j];

				StyleAttribute style = StyleAttrParser.parse(td
						.getAttribute("style"));
				if (style != null) {
					if ("none".equalsIgnoreCase(style.getDisplay())) {
						continue;
					}
				}
				Row row = CellUtil.getRow(rowIndex, sheet);

				int colIndex = row.getPhysicalNumberOfCells();

				CellStyle cellStyle = ExcelUtil.createThinBorderCellStyle(sheet
						.getWorkbook());

				if (style != null) {

					if (style.getWidth() > 0
							|| td.getAttribute("width") != null) {
						sheet.setColumnWidth(colIndex, style.getWidth() * 40);
					}

					if (style.getHeight() > 0) {
						row.setHeight((short) (style.getHeight() * 40));
					}

					if (style.getAlign() != null) {
						short align = CellStyle.ALIGN_CENTER;
						align = "left".equalsIgnoreCase(style.getAlign()) ? CellStyle.ALIGN_LEFT
								: align;
						align = "right".equalsIgnoreCase(style.getAlign()) ? CellStyle.ALIGN_RIGHT
								: align;
						cellStyle.setAlignment(align);
					}

					if (style.getValign() != null) {
						short valign = CellStyle.VERTICAL_CENTER;
						valign = "top".equalsIgnoreCase(style.getValign()) ? CellStyle.VERTICAL_TOP
								: valign;
						valign = "bottom".equalsIgnoreCase(style.getValign()) ? CellStyle.VERTICAL_BOTTOM
								: valign;
						cellStyle.setVerticalAlignment(valign);
					}

				}

				String colspan = td.getAttribute("colspan");
				String rowspan = td.getAttribute("rowspan");

				if (colspan != null || rowspan != null) {
					int lastRow = rowIndex
							+ (rowspan != null && !"0".equals(rowspan) ? Integer
									.parseInt(rowspan) - 1
									: 0);
					int lastColumn = colIndex
							+ (colspan != null && !"0".equals(colspan) ? Integer
									.parseInt(colspan) - 1
									: 0);
					ExcelUtil.addMergedRegion(sheet, rowIndex, lastRow,
							colIndex, lastColumn, cellStyle);
				}

				Cell cell = CellUtil.getCell(row, colIndex);
				cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
				String format = td.getAttribute("format");
				if (format != null && !"".equals(format.trim())) {

					String value = td.toPlainTextString() == null ? "" : td
							.toPlainTextString().trim();

					if (!"".equals(value)) {
						try {
							if (FieldFormatType.number.name().equalsIgnoreCase(
									format)) {
								double d = Double.parseDouble(value);
								if (d != 0.0) {
									cellStyle.setDataFormat(HSSFDataFormat
											.getBuiltinFormat("0.00"));
								} else {
									cellStyle.setDataFormat((short) 1);
								}
								cell.setCellValue(d);
							} else if (FieldFormatType.integer.name()
									.equalsIgnoreCase(format)) {
								cellStyle.setDataFormat((short) 1);
								cell.setCellValue(Integer.parseInt(value));
							} else {
								cellStyle.setDataFormat((short) 0);
								cell.setCellValue(value);
							}
						} catch (Exception e) {
							cell.setCellValue(value);
						}
					}
					cellStyle.setFillForegroundColor(XSLColorUtil
							.getColor(style.getBackgroundColor()));
					cell.setCellStyle(cellStyle);
				} else {
					cellStyle.setDataFormat((short) 0);

					String value = td.toPlainTextString() == null ? "" : td
							.toPlainTextString().trim();
					cell.setCellValue(value);
					if (style != null) {
						cellStyle.setFillForegroundColor(XSLColorUtil
								.getColor(style.getBackgroundColor()));
					} else {
						cellStyle.setFillForegroundColor(XSLColorUtil
								.getColor(""));
					}
					cell.setCellStyle(cellStyle);
				}

			}
		}
	}

	public enum FieldFormatType {
		integer, number, datetime, date, time
	}
}
